package javabean.book;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import javabean.DataBaseConnection;
import javabean.Factory;

public class BookDAOImpl implements BookDAO{

	//录入书本信息
	public int insert(Book book) throws Exception {
		//记录状态：（1）书本已存在或（2）录入成功
		int status = 0;
		DataBaseConnection dbc = new DataBaseConnection() ;
		PreparedStatement pstm=null,pstm1=null,pstm2=null,pstm3=null;
		ResultSet rs = null,rs1=null ;
		String sql = "INSERT INTO book(isbn,bname,author,publisher,years,revision,belong,exterior,picname,picurl,other,type) " +
				"VALUES(?,?,?,?,?,?,?,?,?,?,?,?)" ;
		String sql2 = "SELECT bid,isbn FROM book WHERE belong=?" ;
		String sql1 = null;
		if(book.getQuantity()==0){	//判断图书数量是否大于0，以确定图书可不可借状态
			sql1 = "INSERT INTO book_status(bid,quantity,status) VALUES(?,?,1)" ;
		}
		else{
			sql1 = "INSERT INTO book_status(bid,quantity) VALUES(?,?)" ;
		}
		try{
			pstm2 = dbc.getConnection().prepareStatement(sql2) ;
			pstm2.setString(1, book.getBelong()) ;
			rs = pstm2.executeQuery() ;
			while(rs.next()){
				if(rs.getString(2).equals(book.getIsbn())){
					status = 1 ;
					break ;
				}
			}
			rs.close();
			pstm2.close();
			if(status==0){
				try{
					pstm = dbc.getConnection().prepareStatement(sql) ;
					pstm.setString(1, book.getIsbn()) ;
					pstm.setString(2, book.getBname()) ;
					pstm.setString(3, book.getAuthor()) ;
					pstm.setString(4, book.getPublisher()) ;
					pstm.setString(5, book.getYears()) ;
					pstm.setString(6, book.getRevision()) ;
					pstm.setString(7, book.getBelong()) ;
					pstm.setString(8, book.getExterior()) ;
					pstm.setString(9, book.getPicname()) ;
					pstm.setString(10, book.getPicurl()) ;
					pstm.setString(11, book.getOther()) ;
					pstm.setString(12, book.getType()) ;
					pstm.executeUpdate() ;
					pstm.close() ;
					
					pstm3 = dbc.getConnection().prepareStatement(sql2) ;
					pstm3.setString(1, book.getBelong()) ;
					rs1 = pstm3.executeQuery() ;
					while(rs1.next()){
						if(rs1.getString(2).equals(book.getIsbn())){ //确保书本已经插入数据库了
							pstm1 = dbc.getConnection().prepareStatement(sql1);
							pstm1.setLong(1,rs1.getLong(1));
							pstm1.setLong(2,book.getQuantity()) ;
							pstm1.executeUpdate() ;
							status = 2 ;
							pstm1.close();
							Factory.getUserDAOInstance().updateContribution(book.getBelong()); //更新用户贡献值
							break  ;
						}
					}
					rs1.close() ;
					pstm3.close() ;
					
				}
				catch(Exception e){
					throw new Exception("书本信息录入出错！"+e);
				}
			}
		}
		catch(Exception e){
			throw new Exception("书本信息录入出错！"+e);
		}
		finally{
			dbc.close() ;
		}
		return status ;
	}
	//编辑书本信息
	public boolean edit(Book book) throws Exception {
		//记录状态：（false）编辑失败（true）编辑成功
		boolean status = false;
		DataBaseConnection dbc = new DataBaseConnection() ;
		PreparedStatement pstm=null,pstm1=null;
		String sql = "UPDATE book SET isbn=?,bname=?,author=?,publisher=?,years=?,revision=?,exterior=?,picname=?,picurl=?,other=?,type=? WHERE bid=?";
		String sql1 = null ;
		if(book.getQuantity()>0){	//判断图书数量是否大于0，修改图书可不可借状态
			sql1 = "UPDATE book_status SET quantity=?,status=0 WHERE bid=?";
		}
		else{
			sql1 = "UPDATE book_status SET quantity=?,status=1 WHERE bid=?";
		}
		try{
			pstm = dbc.getConnection().prepareStatement(sql) ;
			pstm.setString(1, book.getIsbn()) ;
			pstm.setString(2, book.getBname()) ;
			pstm.setString(3, book.getAuthor()) ;
			pstm.setString(4, book.getPublisher()) ;
			pstm.setString(5, book.getYears()) ;
			pstm.setString(6, book.getRevision()) ;
			pstm.setString(7, book.getExterior()) ;
			pstm.setString(8, book.getPicname()) ;
			pstm.setString(9, book.getPicurl()) ;
			pstm.setString(10, book.getOther()) ;
			pstm.setString(11, book.getType()) ;
			pstm.setLong(12, book.getBid()) ;
			pstm.executeUpdate() ;
			pstm.close() ;
			
			pstm1 = dbc.getConnection().prepareStatement(sql1);
			pstm1.setInt(1,book.getQuantity()) ;
			pstm1.setLong(2,book.getBid()) ;
			pstm1.executeUpdate() ;
			status = true ;
			pstm1.close();
			
		}
		catch(Exception e){
			System.out.println("书本信息编辑出错");
			throw new Exception("书本信息编辑出错！"+e);
		}
		finally{
			dbc.close() ;
		}
		return status ;
	}
	
	//删除书本信息
	public boolean deleteByBid(Long bid) throws Exception {
		//记录状态：（false）删除失败（true）删除成功
		boolean status = false;
		DataBaseConnection dbc = new DataBaseConnection() ;
		PreparedStatement pstm=null,pstm1=null,pstm2=null;
		String sql = "DELETE FROM book_status WHERE bid=?";
		String sql1 = "DELETE FROM book WHERE bid=?" ;
		String sql2 = "DELETE FROM book_comment WHERE bid=?";
		try{
			pstm = dbc.getConnection().prepareStatement(sql) ;
			pstm.setLong(1, bid) ;
			pstm.executeUpdate() ;
			pstm.close() ;
			pstm2 = dbc.getConnection().prepareStatement(sql2) ;
			pstm2.setLong(1,bid);
			pstm2.executeUpdate();
			pstm2.close();
			pstm1 = dbc.getConnection().prepareStatement(sql1) ;
			pstm1.setLong(1,bid);
			pstm1.executeUpdate();
			pstm1.close();
			status = true;
		}
		catch(Exception e){
			throw new Exception("书本信息删除出错！"+e);
		}
		finally{
			dbc.close() ;
		}
		return status ;
	}
	
	//查询书籍详细信息
	public List<Book> queryAllBookByBid(String bid) throws Exception {
		// TODO Auto-generated method stub
		DataBaseConnection dbc = new DataBaseConnection() ;
		PreparedStatement pstmt = null ;
		ResultSet rs = null ;
		List<Book> all = new ArrayList<Book>();
		
		String sql = "SELECT book.*,book_status.quantity,book_status.status,book_status.borrowcount,book_status.ischeck FROM " +
				"book,book_status WHERE book.bid=book_status.bid AND book.bid=? ORDER BY book.bid DESC" ;
		
		try {
			pstmt = dbc.getConnection().prepareStatement(sql) ;
			pstmt.setString(1, bid) ; 
			rs = pstmt.executeQuery() ;
			while(rs.next()){
				Book book = new Book();
				book.setBid(rs.getLong(1)) ;
				book.setIsbn(rs.getString(2)) ;
				book.setBname(rs.getString(3)) ;
				book.setAuthor(rs.getString(4)) ;
				book.setPublisher(rs.getString(5)) ;
				book.setYears(rs.getString(6)) ;
				book.setRevision(rs.getString(7)) ;
				book.setBelong(rs.getString(8)) ;
				book.setDebit(rs.getString(9)) ;
				book.setExterior(rs.getString(10)) ;
				book.setPicname(rs.getString(11)) ;
				book.setPicurl(rs.getString(12)) ;
				book.setOther(rs.getString(13)) ;
				book.setType(rs.getString(14)) ;
				book.setQuantity(rs.getInt(15)) ;
				book.setStatus(rs.getInt(16)) ;
				book.setBorrowcount(rs.getInt(17)) ;
				book.setIsCheck(rs.getInt(18));
				all.add(book) ;
			}
			rs.close();
			pstmt.close();
			
		} catch (Exception e) {
			// TODO: handle exception
		}
		finally{
			dbc.close() ;
		}
		return all ;
	}
	//查询所有图书
	public List<Book> queryAllBook() throws Exception {
		// TODO Auto-generated method stub
		DataBaseConnection dbc = new DataBaseConnection() ;
		PreparedStatement pstmt = null ;
		ResultSet rs = null ;
		List<Book> all = new ArrayList<Book>();
		String sql = "SELECT * FROM book,book_status WHERE book.bid=book_status.bid ORDER BY book.bid DESC";
		try {
			pstmt = dbc.getConnection().prepareStatement(sql) ;
		
			rs = pstmt.executeQuery() ;
			while(rs.next()){
				Book book = new Book();
				book.setBid(rs.getLong(1)) ;
				book.setIsbn(rs.getString(2)) ;
				book.setBname(rs.getString(3)) ;
				book.setAuthor(rs.getString(4)) ;
				book.setPublisher(rs.getString(5)) ;
				book.setYears(rs.getString(6)) ;
				book.setRevision(rs.getString(7)) ;
				book.setBelong(rs.getString(8)) ;
				book.setDebit(rs.getString(9)) ;
				book.setExterior(rs.getString(10)) ;
				book.setPicname(rs.getString(11)) ;
				book.setPicurl(rs.getString(12)) ;
				book.setOther(rs.getString(13)) ;
				book.setType(rs.getString(14)) ;
				book.setQuantity(rs.getInt(16)) ;
				book.setStatus(rs.getInt(17)) ;
				book.setBorrowcount(rs.getInt(18)) ;
				book.setIsCheck(rs.getInt(19)) ;
				all.add(book) ;
			}
			rs.close();
			pstmt.close();
			
		} catch (Exception e) {
			// TODO: handle exception
		}
		finally{
			dbc.close() ;
		}
		return all ;
	}

	
	//模糊查询书籍总条数（数量大于0，已通过审核）
	public int queryCountBookByLike(String keyword) throws Exception{
		int count = 0 ;
		// TODO Auto-generated method stub
		DataBaseConnection dbc = new DataBaseConnection() ;
		PreparedStatement pstmt = null ;
		ResultSet rs = null ;
		String sql = "SELECT COUNT(*) FROM book,book_status WHERE book.bid=book_status.bid AND (isbn LIKE ? OR bname LIKE ? OR author LIKE ?) AND book_status.quantity>0 AND book_status.ischeck=1";
		try {
			pstmt = dbc.getConnection().prepareStatement(sql) ;
			pstmt.setString(1, "%"+keyword+"%") ;
			pstmt.setString(2, "%"+keyword+"%") ;
			pstmt.setString(3, "%"+keyword+"%") ;
			rs = pstmt.executeQuery() ;
			if(rs.next()){
				count = rs.getInt(1);
			}
			rs.close();
			pstmt.close();
		}catch(Exception ex){
			System.out.println("queryCountBookByLike 查询数据库出错了");
		}
		finally{
			dbc.close();
		}
		return count;
	}
	
	//分段模糊查询（数量大于0,已通过审核）
	public List<Book> queryAllBookByLike(String keyword,int index,int count) throws Exception {
		// TODO Auto-generated method stub
		DataBaseConnection dbc = new DataBaseConnection() ;
		PreparedStatement pstmt = null ;
		ResultSet rs = null ;
		List<Book> all = new ArrayList<Book>();
		String sql = "";
		if(count==-1){
			sql = "SELECT book.*,book_status.quantity,book_status.status,book_status.borrowcount FROM " +
			"book,book_status WHERE book.bid=book_status.bid AND(isbn LIKE ? OR bname LIKE ? OR author LIKE ?) AND book_status.quantity>0 AND book_status.ischeck=1 AND book.bid NOT IN("+
			"SELECT TOP "+(index-1)+" book.bid FROM book,book_status WHERE book.bid=book_status.bid AND(isbn LIKE ? OR bname LIKE ? OR " +
			"author LIKE ?) AND book_status.quantity>0 AND book_status.ischeck=1 ORDER BY book.bid DESC)ORDER BY book.bid DESC" ;
		}else{
			sql = "SELECT TOP "+count+" book.*,book_status.quantity,book_status.status,book_status.borrowcount FROM " +
				"book,book_status WHERE book.bid=book_status.bid AND(isbn LIKE ? OR bname LIKE ? OR author LIKE ?) AND book_status.quantity>0 AND book_status.ischeck=1 AND book.bid NOT IN("+
				"SELECT TOP "+(index-1)+" book.bid FROM book,book_status WHERE book.bid=book_status.bid AND(isbn LIKE ? OR bname LIKE ? OR " +
				"author LIKE ?) AND book_status.quantity>0 AND book_status.ischeck=1 ORDER BY book.bid DESC)ORDER BY book.bid DESC" ;
		}
		
		try {
			pstmt = dbc.getConnection().prepareStatement(sql) ;
			pstmt.setString(1, "%"+keyword+"%") ;
			pstmt.setString(2, "%"+keyword+"%") ;
			pstmt.setString(3, "%"+keyword+"%") ;
			pstmt.setString(4, "%"+keyword+"%") ;
			pstmt.setString(5, "%"+keyword+"%") ;
			pstmt.setString(6, "%"+keyword+"%") ;
			rs = pstmt.executeQuery() ;
			while(rs.next()){
				Book book = new Book();
				book.setBid(rs.getLong(1)) ;
				book.setIsbn(rs.getString(2)) ;
				book.setBname(rs.getString(3)) ;
				book.setAuthor(rs.getString(4)) ;
				book.setPublisher(rs.getString(5)) ;
				book.setYears(rs.getString(6)) ;
				book.setRevision(rs.getString(7)) ;
				book.setBelong(rs.getString(8)) ;
				book.setDebit(rs.getString(9)) ;
				book.setExterior(rs.getString(10)) ;
				book.setPicname(rs.getString(11)) ;
				book.setPicurl(rs.getString(12)) ;
				book.setOther(rs.getString(13)) ;
				book.setType(rs.getString(14)) ;
				book.setQuantity(rs.getInt(15)) ;
				book.setStatus(rs.getInt(16)) ;
				book.setBorrowcount(rs.getInt(17)) ;
				all.add(book) ;
			}
			rs.close();
			pstmt.close();
			
		} catch (Exception e) {
			// TODO: handle exception
		}
		finally{
			dbc.close() ;
		}
		return all ;
	}
	
	//根据图书类型查询书籍总条数（数量大于0,已通过审核）
	public int queryCountBookByType(String type) throws Exception{
		int count = 0 ;
		// TODO Auto-generated method stub
		DataBaseConnection dbc = new DataBaseConnection() ;
		PreparedStatement pstmt = null ;
		ResultSet rs = null ;
		String sql = "SELECT COUNT(*) FROM book,book_status WHERE book.bid=book_status.bid AND book.type=? AND book_status.quantity>0 AND book_status.ischeck=1";
		try {
			pstmt = dbc.getConnection().prepareStatement(sql) ;
			pstmt.setString(1, type) ;
			rs = pstmt.executeQuery() ;
			if(rs.next()){
				count = rs.getInt(1);
			}
			rs.close();
			pstmt.close();
		}catch(Exception ex){
			System.out.println("queryCountBookByType 查询数据库出错了");
		}
		finally{
			dbc.close();
		}
		return count;
	}
	
	//根据图书类型分段查询（数量大于0,已通过审核）
	public List<Book> queryAllBookByType(String type,int index,int count) throws Exception {
		// TODO Auto-generated method stub
		DataBaseConnection dbc = new DataBaseConnection() ;
		PreparedStatement pstmt = null ;
		ResultSet rs = null ;
		List<Book> all = new ArrayList<Book>();
		String sql = "";
		if(count==-1){
			sql = "SELECT book.*,book_status.quantity,book_status.status,book_status.borrowcount FROM " +
			"book,book_status WHERE book.bid=book_status.bid AND book.type=? AND book_status.quantity>0 AND book_status.ischeck=1 AND book.bid NOT IN("+
			"SELECT TOP "+(index-1)+" book.bid FROM book,book_status WHERE book.bid=book_status.bid AND book.type=? AND book_status.quantity>0 AND book_status.ischeck=1 ORDER BY book.bid DESC)ORDER BY book.bid DESC" ;
		}else{
			sql = "SELECT TOP "+count+" book.*,book_status.quantity,book_status.status,book_status.borrowcount FROM " +
				"book,book_status WHERE book.bid=book_status.bid AND book.type=? AND book_status.quantity>0 AND book_status.ischeck=1 AND book.bid NOT IN("+
				"SELECT TOP "+(index-1)+" book.bid FROM book,book_status WHERE book.bid=book_status.bid AND book.type=? AND book_status.quantity>0 AND book_status.ischeck=1 ORDER BY book.bid DESC)ORDER BY book.bid DESC" ;
		}
		try {
			pstmt = dbc.getConnection().prepareStatement(sql) ;
			pstmt.setString(1, type) ;
			pstmt.setString(2, type) ;
			rs = pstmt.executeQuery() ;
			while(rs.next()){
				Book book = new Book();
				book.setBid(rs.getLong(1)) ;
				book.setIsbn(rs.getString(2)) ;
				book.setBname(rs.getString(3)) ;
				book.setAuthor(rs.getString(4)) ;
				book.setPublisher(rs.getString(5)) ;
				book.setYears(rs.getString(6)) ;
				book.setRevision(rs.getString(7)) ;
				book.setBelong(rs.getString(8)) ;
				book.setDebit(rs.getString(9)) ;
				book.setExterior(rs.getString(10)) ;
				book.setPicname(rs.getString(11)) ;
				book.setPicurl(rs.getString(12)) ;
				book.setOther(rs.getString(13)) ;
				book.setType(rs.getString(14)) ;
				book.setQuantity(rs.getInt(15)) ;
				book.setStatus(rs.getInt(16)) ;
				book.setBorrowcount(rs.getInt(17)) ;
				all.add(book) ;
			}
			rs.close();
			pstmt.close();
			
		} catch (Exception e) {
			// TODO: handle exception
		}
		finally{
			dbc.close() ;
		}
		return all ;
	}
	
	//查询用户个人图书
	public List<Book> queryAllBookByUid(String uid) throws Exception {
		// TODO Auto-generated method stub
		DataBaseConnection dbc = new DataBaseConnection() ;
		PreparedStatement pstmt = null ;
		ResultSet rs = null ;
		List<Book> all = new ArrayList<Book>();
		String sql = "SELECT * FROM book,book_status WHERE book.bid=book_status.bid AND book.belong=? ORDER BY book.bid DESC";
		try {
			pstmt = dbc.getConnection().prepareStatement(sql) ;
			pstmt.setString(1, uid) ;
			rs = pstmt.executeQuery() ;
			while(rs.next()){
				Book book = new Book();
				book.setBid(rs.getLong(1)) ;
				book.setIsbn(rs.getString(2)) ;
				book.setBname(rs.getString(3)) ;
				book.setAuthor(rs.getString(4)) ;
				book.setPublisher(rs.getString(5)) ;
				book.setYears(rs.getString(6)) ;
				book.setRevision(rs.getString(7)) ;
				book.setBelong(rs.getString(8)) ;
				book.setDebit(rs.getString(9)) ;
				book.setExterior(rs.getString(10)) ;
				book.setPicname(rs.getString(11)) ;
				book.setPicurl(rs.getString(12)) ;
				book.setOther(rs.getString(13)) ;
				book.setType(rs.getString(14)) ;
				book.setQuantity(rs.getInt(16)) ;
				book.setStatus(rs.getInt(17)) ;
				book.setBorrowcount(rs.getInt(18)) ;
				book.setIsCheck(rs.getInt(19)) ;
				all.add(book) ;
			}
			rs.close();
			pstmt.close();
		}catch(Exception ex){
			System.out.println("查询用户个人图书数据库出错");
		}
		finally{
			dbc.close() ;
		}
		return all;
	}
	
	//查询用户所借图书
	public List<Book> queryDebitBookByUid(String uid) throws Exception{
		// TODO Auto-generated method stub
		DataBaseConnection dbc = new DataBaseConnection() ;
		PreparedStatement pstmt = null ;
		ResultSet rs = null ;
		List<Book> all = new ArrayList<Book>();
		String sql = "SELECT * FROM book,book_status WHERE book.bid=book_status.bid AND book.debit=?";
		try {
			pstmt = dbc.getConnection().prepareStatement(sql) ;
			pstmt.setString(1, uid) ;
			rs = pstmt.executeQuery() ;
			while(rs.next()){
				Book book = new Book();
				book.setBid(rs.getLong(1)) ;
				book.setIsbn(rs.getString(2)) ;
				book.setBname(rs.getString(3)) ;
				book.setAuthor(rs.getString(4)) ;
				book.setPublisher(rs.getString(5)) ;
				book.setYears(rs.getString(6)) ;
				book.setRevision(rs.getString(7)) ;
				book.setBelong(rs.getString(8)) ;
				book.setDebit(rs.getString(9)) ;
				book.setExterior(rs.getString(10)) ;
				book.setPicname(rs.getString(11)) ;
				book.setPicurl(rs.getString(12)) ;
				book.setOther(rs.getString(13)) ;
				book.setType(rs.getString(14)) ;
				book.setQuantity(rs.getInt(16)) ;
				book.setStatus(rs.getInt(17)) ;
				book.setBorrowcount(rs.getInt(18)) ;
				book.setIsCheck(rs.getInt(19)) ;
				all.add(book) ;
			}
			rs.close();
			pstmt.close();
		}catch(Exception ex){
			System.out.println("查询用户个人图书数据库出错");
		}
		finally{
			dbc.close() ;
		}
		return all;
	}
	
	//查询图书分类
	public List<BookTag> queryAllBookTag() throws Exception{
		// TODO Auto-generated method stub
		DataBaseConnection dbc = new DataBaseConnection() ;
		PreparedStatement pstmt = null ;
		ResultSet rs = null ;
		List<BookTag> all = new ArrayList<BookTag>();
		String sql = "SELECT * FROM book_tag ORDER BY readcount DESC";
		try {
			pstmt = dbc.getConnection().prepareStatement(sql) ;
			rs = pstmt.executeQuery() ;
			while(rs.next()){
				BookTag bookTag = new BookTag();
				bookTag.setTagname(rs.getString(1)) ;
				bookTag.setReadcount(rs.getLong(2)) ;
				List<Book> book = this.queryAllBookByType(rs.getString(1), 1, -1);
				bookTag.setBook(book);
				all.add(bookTag) ;
			}
			rs.close();
			pstmt.close();
			
		} catch (Exception e) {
			// TODO: handle exception
		}
		finally{
			dbc.close() ;
		}
		return all ;
	}
	
	//更新图书分类访问数量
	public boolean updateBookTagReadCount(String tagname) throws Exception{
		// TODO Auto-generated method stub
		DataBaseConnection dbc = new DataBaseConnection() ;
		PreparedStatement pstmt1 = null, pstmt2 = null ;
		ResultSet rs = null ;
		boolean isSuccess = false;
		String sql1 = "SELECT readcount FROM book_tag WHERE tagname=?";
		String sql2 = "UPDATE book_tag SET readcount=? WHERE tagname=?";
		try {
			pstmt1 = dbc.getConnection().prepareStatement(sql1) ;
			pstmt1.setString(1,tagname);
			rs = pstmt1.executeQuery() ;
			if(rs.next()){
				pstmt2 = dbc.getConnection().prepareStatement(sql2) ;
				pstmt2.setLong(1,rs.getLong(1)+1);
				pstmt2.setString(2,tagname);
				pstmt2.executeUpdate();
				isSuccess = true;
				pstmt2.close();
			}
			rs.close();
			pstmt1.close();
			
		} catch (Exception e) {
			// TODO: handle exception
		}
		finally{
			dbc.close() ;
		}
		return isSuccess ;
	}
	
	//查询图书全部回复内容（评分）
	public List<BookComment> queryBookCommentByBid(String bid) throws Exception{
		DataBaseConnection dbc = new DataBaseConnection() ;
		PreparedStatement pstmt = null ;
		ResultSet rs = null ;
		List<BookComment> all = new ArrayList<BookComment>();
		String sql = "SELECT book_comment.*,users.pic,users.introduce FROM book_comment,users WHERE book_comment.bid=? AND book_comment.uid=users.uid";
		try {
			pstmt = dbc.getConnection().prepareStatement(sql) ;
			pstmt.setString(1, bid) ;
			rs = pstmt.executeQuery() ;
			while(rs.next()){
				BookComment bookComment = new BookComment();
				bookComment.setBcid(rs.getLong(1)) ;
				bookComment.setBccontents(rs.getString(2)) ;
				bookComment.setBid(rs.getLong(3));
				bookComment.setBctime(rs.getString(4));
				bookComment.setUid(rs.getString(5));
				bookComment.setScore(rs.getInt(6));
				bookComment.setPic(rs.getString(7));
				bookComment.setIntroduce(rs.getString(8));
				all.add(bookComment) ;
			}
			rs.close();
			pstmt.close();
			
		} catch (Exception e) {
			// TODO: handle exception
		}
		finally{
			dbc.close() ;
		}
		return all ;
	}
	//查询图书对应用户单条回复内容（评分）
	public List<BookComment> queryBookCommentByBidAndUid(String bid,String uid) throws Exception{
		DataBaseConnection dbc = new DataBaseConnection() ;
		PreparedStatement pstmt = null ;
		ResultSet rs = null ;
		List<BookComment> all = new ArrayList<BookComment>();
		String sql = "SELECT * FROM book_comment WHERE bid=? AND uid=? AND score IS NOT NULL";
		try {
			pstmt = dbc.getConnection().prepareStatement(sql) ;
			pstmt.setString(1, bid) ;
			pstmt.setString(2, uid) ;
			rs = pstmt.executeQuery() ;
			if(rs.next()){
				BookComment bookComment = new BookComment();
				bookComment.setBcid(rs.getLong(1)) ;
				bookComment.setBccontents(rs.getString(2)) ;
				bookComment.setBid(rs.getLong(3));
				bookComment.setBctime(rs.getString(4));
				bookComment.setUid(rs.getString(5));
				bookComment.setScore(rs.getInt(6));
				all.add(bookComment) ;
			}
			rs.close();
			pstmt.close();
			
		} catch (Exception e) {
			// TODO: handle exception
		}
		finally{
			dbc.close() ;
		}
		return all;
	}
	//图书评分
	public boolean giveBookScore(BookComment bookComment) throws Exception{
		boolean isSuccess = false;
		DataBaseConnection dbc = new DataBaseConnection() ;
		PreparedStatement pstmt = null;
		String sql = "";
		if(this.isGivedScore(bookComment.getUid(), bookComment.getBid())){
			sql = "UPDATE book_comment SET bctime=?,score=? WHERE uid=? AND bid=? AND score IS NOT NULL";
		}else{
			sql = "INSERT INTO book_comment(bctime,score,uid,bid) VALUES(?,?,?,?)";
		}
		try{
			pstmt = dbc.getConnection().prepareStatement(sql) ;
			pstmt.setString(1, bookComment.getBctime()) ;
			pstmt.setInt(2, bookComment.getScore()) ;
			pstmt.setString(3, bookComment.getUid()) ;
			pstmt.setLong(4, bookComment.getBid());
			pstmt.executeUpdate();
			isSuccess = true;
			pstmt.close();
		}catch(Exception ex){
			System.out.println("图书评分数据库出错");
		}
		finally{
			dbc.close() ;
		}
		return isSuccess;
	}
	//删除图书评分
	public boolean deleteBookScore(String bid,String uid) throws Exception{
		boolean isSuccess = false;
		DataBaseConnection dbc = new DataBaseConnection() ;
		PreparedStatement pstmt = null;
		String sql = "DELETE FROM book_comment WHERE bid=? AND uid=? AND score IS NOT NULL";
		try{
			pstmt = dbc.getConnection().prepareStatement(sql) ;
			pstmt.setString(1,bid);
			pstmt.setString(2,uid);
			pstmt.executeUpdate();
			isSuccess = true;
			pstmt.close();
		}catch(Exception ex){
			System.out.println("删除图书评分数据库出错");
		}
		finally{
			dbc.close() ;
		}
		return isSuccess;
	}
	//检查是否已评分
	public boolean isGivedScore(String uid,Long bid) throws Exception{
		boolean isExist = false;
		DataBaseConnection dbc = new DataBaseConnection() ;
		PreparedStatement pstmt = null ;
		ResultSet rs = null ;
		String sql = "SELECT * FROM book_comment WHERE uid=? AND bid=? AND score IS NOT NULL";
		try{
			pstmt = dbc.getConnection().prepareStatement(sql) ;
			pstmt.setString(1, uid) ;
			pstmt.setLong(2, bid);
			rs = pstmt.executeQuery() ;
			if(rs.next()){
				isExist = true;
			}
			rs.close();
			pstmt.close();
		}catch(Exception ex){
			System.out.println("检查是否已评分数据库出错");
		}
		finally{
			dbc.close() ;
		}
		return isExist;
	}
	
	//图书评论
	public long giveBookComment(BookComment bookComment) throws Exception{
		long bcid = 0;
		DataBaseConnection dbc = new DataBaseConnection() ;
		PreparedStatement pstmt = null, pstmt2=null;
		ResultSet rs = null;
		String sql = "INSERT INTO book_comment(bctime,bccontents,uid,bid) VALUES(?,?,?,?)";
		String sql2 = "SELECT bcid FROM book_comment WHERE bctime=?";
		try{
			pstmt = dbc.getConnection().prepareStatement(sql) ;
			pstmt.setString(1, bookComment.getBctime()) ;
			pstmt.setString(2, bookComment.getBccontents()) ;
			pstmt.setString(3, bookComment.getUid()) ;
			pstmt.setLong(4, bookComment.getBid());
			pstmt.executeUpdate();
			pstmt.close();
			try{
				pstmt2 = dbc.getConnection().prepareStatement(sql2);
				pstmt2.setString(1,bookComment.getBctime());
				rs = pstmt2.executeQuery();
				if(rs.next()){
					bcid = rs.getLong(1);
				}
			}catch(Exception ex){}
		}catch(Exception ex){
			System.out.println("图书评论数据库出错");
		}
		finally{
			dbc.close() ;
		}
		return bcid;
	}
	//删除图书评论
	public boolean deleteBookComment(String bcid) throws Exception{
		boolean isSuccess = false;
		DataBaseConnection dbc = new DataBaseConnection() ;
		PreparedStatement pstmt = null;
		String sql = "DELETE FROM book_comment WHERE bcid=?";
		try{
			pstmt = dbc.getConnection().prepareStatement(sql) ;
			pstmt.setString(1,bcid);
			pstmt.executeUpdate();
			isSuccess = true;
			pstmt.close();
		}catch(Exception ex){
			System.out.println("删除图书评论数据库出错");
		}
		finally{
			dbc.close() ;
		}
		return isSuccess;
	}
	
	//更改图书审核状态
	public boolean changeBookIsCheck(String bid,int ischeck) throws Exception{
		boolean isSuccess = false;
		DataBaseConnection dbc = new DataBaseConnection() ;
		PreparedStatement pstmt = null;
		String sql = "UPDATE book_status SET ischeck=? WHERE bid=? ";
		try{
			pstmt = dbc.getConnection().prepareStatement(sql) ;
			pstmt.setInt(1,ischeck);
			pstmt.setString(2, bid);
			pstmt.executeUpdate();
			isSuccess = true;
			pstmt.close();
		}catch(Exception ex){
			System.out.println("更改图书审核状态数据库出错");
		}
		finally{
			dbc.close() ;
		}
		return isSuccess;
	}
}
